package com.zr.gdms.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.zr.gdms.dao.StudentInfoDao;
import com.zr.gdms.model.Student;
import com.zr.gdms.util.JDBCUtil;

public class StudentInfoDaoImpl implements StudentInfoDao {

	@Override
	public List<Student> allStudentInfo(int tid) {
		List<Student> ls = new ArrayList<Student>();
		StringBuffer str = new StringBuffer("");
		str.append("SELECT student.sid,student.sname,student.sage,student.ssex,");
		str.append("student.sdepart,student.stel");
		str.append(" FROM student");
		str.append(" INNER JOIN score");
		str.append(" ON score.sid =student.sid");
		str.append(" INNER JOIN course");
		str.append(" ON course.cid = score.cid");
		str.append(" INNER JOIN teacher");
		str.append(" ON course.tid=teacher.tid");
		str.append(" WHERE teacher.tid=?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(str.toString());
			pst.setInt(1, tid);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setSid(rs.getInt("sid"));
				s.setSname(rs.getString("sname"));
				s.setSage(rs.getString("sage"));
				s.setSsex(rs.getString("ssex"));
				s.setSdepart(rs.getString("sdepart"));
				s.setStel(rs.getString("stel"));
			    ls.add(s);		
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ls;
	}

	@Override
	public List<Student> allStudent() {
		List<Student> ls1 = new ArrayList<Student>();
		StringBuffer str = new StringBuffer("");
		str.append("SELECT student.sid,student.sname,student.sage,student.ssex,");
		str.append("student.sdepart,student.stel");
		str.append(" FROM student");
		str.append(" INNER JOIN score");
		str.append(" ON score.sid =student.sid");
		str.append(" INNER JOIN course");
		str.append(" ON course.cid = score.cid");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(str.toString());
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setSid(rs.getInt("sid"));
				s.setSname(rs.getString("sname"));
				s.setSage(rs.getString("sage"));
				s.setSsex(rs.getString("ssex"));
				s.setSdepart(rs.getString("sdepart"));
				s.setStel(rs.getString("stel"));
			    ls1.add(s);		
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ls1;
	}

	@Override
	public boolean deletstuBysid(int sid) {
		boolean flag = false;
		StringBuilder str = new StringBuilder("");
		   str.append(" DELETE FROM student");
		   str.append(" WHERE student.sid=?");
		   Connection con = JDBCUtil.getConnection();
		   PreparedStatement pst;
		   try {
			pst = con.prepareStatement(str.toString());
			pst.setInt(1, sid);
			int i = pst.executeUpdate();
			if (i>0) {
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

}
